# -*-coding:utf8 -*-
# filename:linksql.py
import sqlite3
import time
import sms
import weixin
import logging

logger = logging.getLogger()  # 全局的logger


def alipay(phone, name, openid):  # 支付宝账号绑定,此处要防止sql注入
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % openid,)
    curs.execute('SELECT * FROM user WHERE openid=?', args)
    sqldata = curs.fetchone()
    if sqldata:
        args = ("%s" % phone, "%s" % name, "%s" % openid)
        curs.execute('UPDATE user SET alipay=?, xingming=? WHERE openid =?', args)  # 使用预编译方法防止输入
        conn.commit()
        conn.close()
        return True
    else:
        conn.close()
        return False


def setProxy(openid, proxy, force=False):  # 设置代理
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('SELECT proxy FROM user WHERE openid=?;', args)
    sqldata = curs.fetchone()
    if sqldata[0] and force == False:
        conn.close()
        return False
    else:
        args = ("%s" % (proxy), "%s" % (openid))
        curs.execute('UPDATE user SET proxy=? WHERE openid=?;', args)
        conn.commit()
        conn.close()
        return True


def checkalipay(openid):  # 是否可以提现
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % openid,)
    curs.execute('SELECT * FROM user WHERE openid=? AND alipay IS NOT NULL;', args)
    sqldata = curs.fetchone()
    if sqldata:
        conn.close()
        return True
    else:
        conn.close()
        return False


def publisher(openid):  # 检查是否已备案渠道 有备案则返回渠道关系id
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('SELECT relation_id FROM beian WHERE openid =?;', args)
    sqldata = curs.fetchone()
    if sqldata:
        return sqldata[0]


def openidpid(openid):  # 从openid获得pid，用于优惠券查询等
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('SELECT adzone_id FROM pid WHERE openid =?', args)
    sqldata = curs.fetchone()
    if sqldata:
        return sqldata[0]
    else:
        return None


def checktixian(openid):  # 提现资格申请
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('SELECT * FROM user WHERE openid=? AND rgoods > qiandao AND orders > 3;', args)
    sqldata = curs.fetchone()
    if sqldata:
        conn.close()
        return True
    else:
        conn.close()
        return False


def tixian(openid):  # 提现申请
    user = sqltbk(openid)
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ('%f' % (user.remain), '%s' % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())),
            '%f' % (user.remain + user.cashed), '%s' % (openid))
    curs.execute('UPDATE user SET cash=?, remain=0, cashed_time=?, cashed=? WHERE openid=?;', args)
    curs.fetchall()
    conn.commit()
    conn.close()
    return None


def sqltbk(openid):  # 从openid返回用户详细信息，如果该openid不存在则创建该openid的新用户
    conn = sqlite3.connect('taobaokesql.db')
    # conn.row_factory = dict_factory
    curs = conn.cursor()
    if isnewuser(openid):  # 新用户
        logger.info(openid + ' 新用户')
        newuser(openid)
    args = (openid,)
    curs.execute("SELECT * FROM user WHERE openid=?;", args)
    sqldata = curs.fetchone()
    conn.close()
    return sqlUser(sqldata)


def removeUser(openid):  # 删除openid下的用户信息
    if isnewuser(openid):
        return {'error': 1, 'msg': '用户不存在'}
    conn = sqlite3.connect('taobaokesql.db')
    # conn.text_factory = str
    curs = conn.cursor()
    args = (openid,)
    curs.execute('DELETE FROM user WHERE openid =?;', args)
    conn.commit()
    conn.close()
    if isnewuser(openid):
        return {'error': 0, 'msg': '删除成功，如果有错请在备份中找回'}
    else:
        return {'error': 2, 'msg': '删除失败，请检查函数和openid'}


def isnewuser(openid):  # 判断用户是否存在
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    query = 'SELECT * FROM user WHERE openid="%s";' % openid
    curs.execute(query)
    sqldata = curs.fetchone()
    conn.close()
    if sqldata:
        return False
    else:
        return True


def newuser(openid):  # 创建一个新用户
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    query = 'INSERT INTO user(openid, level, cashed, remain, disabled, orders, num, cash, goods, qiandao, level_coefficient,rgoods,gift) VALUES ("%s","%s",%f,%f,%f,%d,%d,%f,%f,%f, %f, %f, %f);' % (
    openid, '二级用户', 0, 0, 0, 0, 0, 0, 0, 0, 0.55, 0, 0)
    curs.execute(query)
    # level = "二级用户"
    # level_coefficient = 0.55
    # args = (openid,level,level_coefficient,)
    # conn.text_factory = str
    # curs.execute("INSERT INTO user(openid,level,level_coefficient) VALUES (?,?,?)",args)
    args = ("o1mDH1HT8Nf7EdDc2-WJbw8mXkvk", openid)
    curs.execute("UPDATE user SET proxy =? WHERE openid = ?", args)
    conn.commit()
    conn.close()
    return {'error': 0, 'openid': openid, 'msg': '新用户'}


# 通过unionId保存微信小程序的session_key,
# 其实也就是登录的过程,需要保存的内容有session_key，session_key_date,
# 在保存session_key之前应当校验wx_miniprogram_openid是否正确
def wx_sessionKey_save(unionId, session_key):
    timestr = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    if not (unionId):
        return session_key
    conn = sqlite3.connect('taobaokesql.db')
    conn.row_factory = dict_factory
    curs = conn.cursor()
    args = (session_key, timestr, unionId)
    curs.execute("UPDATE user SET wx_session_key=? wx_sessionKey_date=? WHERE wx_unionid=?", args)
    conn.commit()
    conn.close()
    return None


class sqlUser(object):  # 以属性的方式展示用户的信息
    def __init__(self, sqldata):
        self.openid = sqldata[0]
        self.level = sqldata[1]
        self.special_id = sqldata[2]  # 来自淘宝联盟的渠道关系id
        self.cashed = sqldata[3]  # 已提现金额 float
        self.remain = sqldata[4]
        self.disabled = sqldata[5]  # 冻结金额 float
        self.orders = sqldata[6]  # 订单数 smallint
        self.proxy = sqldata[7]  # 代理 上级的special_id
        self.proxy_money = sqldata[8]  # 代理列表
        self.num = sqldata[9]  # 代理数 smallint
        self.cash = sqldata[10]  # 提现中金额
        self.lastdate = sqldata[11]  # 最后签到日期
        self.cashed_time = sqldata[12]
        self.last_order = sqldata[13]
        self.goods = sqldata[14]
        self.qiandao = sqldata[15]
        self.alipay = sqldata[16]
        self.xingming = sqldata[17]
        self.level_coefficient = sqldata[18]  # 等级系数
        self.rgoods = sqldata[21]  # 已收货的金额
        self.gift = sqldata[22]  # 由系统赠送的金额，通常是bug的平衡处理


def qiandao(openid, qian):  # 签到金额加入数据库
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    query = 'UPDATE user SET qiandao=qiandao + %f WHERE openid="%s";' % (qian, openid)
    curs.execute(query)
    query = 'UPDATE user SET lastdate="%s" WHERE openid="%s";' % (time.strftime('%Y-%m-%d', time.localtime()), openid)
    curs.execute(query)
    conn.commit()
    conn.close()
    return None


def monthorder():  # 更新本月的所有订单
    # 吐槽一下这个方法。每个订单都要查询一次真的是太不科学了。一方面如果多个订单在同一天或20分钟内就应该一次查完。另一方面 有些订单在本月已经提前结算了，难道就为了预防维权订单而每天对大量已收货订单进行查询？太不科学了。
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    localtime = time.localtime(time.time())
    tmday = localtime[2]
    if int(tmday) >= 21:  # 21号月结
        query = 'SELECT * FROM orders WHERE create_time like "%s";' % (
            time.strftime("%Y-%m%", time.localtime()))  # 选择本月订单
    else:
        tmmonth = time.strftime("%Y-%m%", time.localtime())
        query = 'SELECT * FROM orders WHERE create_time like "%s" OR create_time like "%s";' % (
        time.strftime("%Y-%m%", time.localtime(time.time() - 2592000)), tmmonth)  # 一个月有2592000秒  这里包括了本月订单及上个月的所有订单
    curs.execute(query)
    orders = curs.fetchall()
    return orders


def updateorder(order):  # 更新订单入库
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    if 'tk_earning_time' in order:
        query = 'UPDATE orders SET pay_price=%f, commission=%f, earning_time="%s", tk_status="%s", click_time="%s" WHERE trade_id = "%s";' % (
        float(order['pay_price']), float(order['pub_share_fee']), order['tk_earning_time'], order['tk_status'],
        order['tk_create_time'], order['trade_id'])
    else:
        query = 'UPDATE orders SET commission=%f, tk_status="%s", click_time="%s" WHERE trade_id = "%s";' % (
        float(order['pub_share_fee']), order['tk_status'], order['tk_create_time'], order['trade_id'])
    curs.execute(query)
    conn.commit()
    conn.close()
    logger.info('订单更新 ' + str(order['trade_id']) + ' ' + order['tk_create_time'])
    return str(order['trade_id'])  # 成功则返回订单号


def cash(openid):  # 完成用户提现
    if not (iscashing(openid)):
        return {'error': 1, 'msg': '用户未申请提现'}
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    timestr = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    args = (timestr, openid)
    curs.execute('UPDATE user SET cash=0, cashed_time=? WHERE openid =?;', args)
    conn.commit()
    conn.close()
    if iscashing(openid):
        return {'error': 2, 'msg': '提现出错，请检查函数'}
    else:
        logger.info(openid + ' 提现成功')
        return {'error': 0, 'msg': '提现成功'}


def completed_tixian(xingming, openid):  # 提现
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    timestr = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    query = 'SELECT openid FROM user WHERE openid LIKE "%s";' % ('%' + openid)
    curs.execute(query)
    sqldata = curs.fetchone()
    newopenid = sqldata[0]
    query = 'UPDATE user SET cash=0, cashed_time="%s" WHERE xingming="%s" AND openid = "%s";' % (
    timestr, xingming, newopenid)
    curs.execute(query)
    query = 'SELECT cashed_time FROM user WHERE openid = "%s";' % (newopenid)
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:  # openid存在
        if sqldata[0] == timestr:  # 修改成功
            conn.commit()
            conn.close()
            return True
        else:
            conn.close()
            return False
    else:
        conn.close()
        return False


# 更新代理中获得的金额proxy_money
def updateProxyMoney(openid):
    proxy_coefficient = 0.25  # 代理系数
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    conn.row_factory = dict_factory
    args = (proxy_coefficient, openid,)
    curs.execute("SELECT SUM(cashed)*? FROM user WHERE proxy=?;", args)
    sqldata = curs.fetchone()
    if (sqldata):
        # 保存
        if sqldata[0]:
            proxy_money = sqldata[0]
        else:
            proxy_money = 0
        args = (proxy_money, openid,)
        curs.execute("UPDATE user SET proxy_money = ? WHERE openid = ?", args)
        conn.commit()
    conn.close()
    return sqldata


# 更新代理数
def updateProxyNum(openid):
    conn = sqlite3.connect("taobaokesql.db")
    curs = conn.cursor()
    conn.row_factory = dict_factory
    args = (openid,)
    curs.execute("SELECT COUNT(*) FROM user WHERE proxy=?", args)
    sqldata = curs.fetchone()
    if (sqldata):
        # 保存
        proxyNum = sqldata[0]
        args = (proxyNum, openid,)
        curs.execute("UPDATE user SET num = ? WHERE openid = ?", args)
        conn.commit()
    conn.close()
    return sqldata


# 更新用户的remain orders
def updateUser(openid):  # 更新用户的remain orders
    user = sqltbk(openid)
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    # 用户信息更新
    query = 'SELECT COUNT(*) FROM user WHERE proxy="%s";' % (openid)
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        num = int(sqldata[0])
    else:
        num = 0
    query = 'UPDATE user SET num =%d WHERE openid = "%s";' % (num, openid)
    curs.execute(query)

    # 订单信息更新
    query = 'SELECT count(*) FROM ORDERS WHERE  (tk_status=12 or tk_status=3 or tk_status=14) AND openid="%s";' % (
        openid)  # 总订单数
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        money_orders = int(sqldata[0])
        query = 'UPDATE user SET orders=%d WHERE openid="%s";' % (money_orders, openid)
        curs.execute(query)
    else:
        query = 'UPDATE user SET orders=0 WHERE openid="%s";' % (openid)
        curs.execute(query)
    query = 'SELECT sum(pub_share_pre_fee) FROM orders WHERE (tk_status=3 or tk_status=14) and openid="%s";' % (
        openid)  # 3订单结算 14 订单成功 总可用收入
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        money_enabled = float(sqldata[0])
        money_rgoods = money_enabled * user.level_coefficient  # 已收货收入
        query = 'UPDATE user SET rgoods=%f WHERE openid="%s";' % (money_rgoods, openid)
        curs.execute(query)
        money_remain = money_rgoods - float(user.cashed) + float(user.qiandao) + float(user.gift)  # 可用收入= 总收入 - 已提现收入
        query = 'UPDATE user SET remain=%f WHERE openid="%s";' % (money_remain, openid)
        curs.execute(query)
    else:
        money_remain = float(user.qiandao) + float(user.gift) - float(user.cashed)
        query = 'UPDATE user SET remain=%f WHERE openid="%s";' % (money_remain, openid)
        curs.execute(query)
    query = 'SELECT sum(pub_share_pre_fee) FROM orders WHERE tk_status=12 and openid="%s";' % (openid)  # 12 订单付款 未收货收入
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        money_goods = float(sqldata[0]) * user.level_coefficient
        query = 'UPDATE user SET goods=%f WHERE openid="%s";' % (money_goods, openid)
        curs.execute(query)
    else:
        query = 'UPDATE user SET goods=0 WHERE openid="%s";' % (openid)
        curs.execute(query)
    query = 'SELECT sum(pub_share_pre_fee) FROM orders WHERE tk_status=13 and openid="%s";' % (openid)  # 13 订单失效 失败订单收入
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        money_disabled = float(sqldata[0]) * user.level_coefficient
        query = 'UPDATE user SET disabled=%f WHERE openid="%s";' % (money_disabled, openid)
        curs.execute(query)
    else:
        query = 'UPDATE user SET disabled=0 WHERE openid="%s";' % (openid)
        curs.execute(query)
    conn.commit()
    conn.close()
    # 更新代理金额
    updateProxyMoney(openid)
    updateProxyNum(openid)
    return "done!"


def pidopenid(adzone_id):  # pid模式 从pid获取openid
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    conn.row_factory = dict_factory  # 使数据库直接返回字典
    query = 'SELECT * FROM pid WHERE adzone_id="' + str(adzone_id) + '"'
    curs.execute(query)
    sqldata = curs.fetchone()
    conn.commit()
    conn.close()
    return sqldata


def removepid(openid):  # 限已有rid 的情况，可以删除pid，为下一步全面接入rid作准备
    if not (openidrid(openid)):
        return {'error': 11, 'msg': 'no rid'}
    if not (openidpid(openid)):
        return {'error': 12, 'msg': 'no pid'}
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('DELETE FROM pid WHERE openid=?;', args)
    conn.commit()
    conn.close()


def openidrid(openid):  # 从openid获得rid用来拼接优惠券。
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (openid),)
    curs.execute('SELECT relation_id FROM beian WHERE openid =?;', args)
    sqldata = curs.fetchone()
    if sqldata:
        conn.close()
        return sqldata[0]
    else:
        conn.close()


def ridopenid(relation_id):  # rid模式 从relation_id 获取 openid
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = ("%s" % (relation_id),)
    curs.execute('SELECT openid FROM beian WHERE relation_id =?', args)
    sqldata = curs.fetchone()
    if sqldata:
        return sqldata


def getuserid(openid):
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    query = 'SELECT userid FROM user WHERE openid ="%s";' % (openid)
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata[0]:
        userid = sqldata[0]
        conn.commit()
        conn.close()
        return userid
    else:
        conn.close()
        return None


def iscashing(openid):
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    args = (openid,)
    curs.execute('SELECT * FROM user WHERE cash > 0 AND openid=?;', args)
    sqldata = curs.fetchone()
    conn.close()
    if sqldata:
        return True
    else:
        return False


def dict_factory(cursor, row):  # 这个是slqite3官方的把select结果以字典输出的方法函数；表示看不懂。
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


def app_userinfo(openid):  # 查询用户信息app
    updateUser(openid)
    conn = sqlite3.connect('taobaokesql.db')
    conn.row_factory = dict_factory
    curs = conn.cursor()
    args = (openid,)
    curs.execute('SELECT * FROM user WHERE openid =?;', args)
    sqldata = curs.fetchone()
    conn.close()
    return sqldata


def cashing():
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    query = 'SELECT * FROM user WHERE cash > 0;'
    curs.execute(query)
    sqldata = curs.fetchall()
    textdata = []
    if sqldata:
        for user in sqldata:
            if user[17]:
                text = {"openid": user[0], "xingming": user[17]}
                textdata.append(text)
    conn.close()
    return textdata


def getpid(openid):
    if not (isnewuser(openid)):  # 存在绑定关系，无需再生成pid关系
        return None
    conn = sqlite3.connect('taobaokesql.db')
    conn.row_factory = dict_factory
    curs = conn.cursor()
    query = 'SELECT * FROM pid ORDER BY create_time desc limit 0,1'  # 按时间降序，取最新一条
    curs.execute(query)
    sqldata = curs.fetchone()
    if not (sqldata):  # pid表为空
        new_id = 1
        query = 'SELECT * FROM pidku WHERE id=1'
        curs.execute(query)
    else:
        last_id = sqldata[6]  # 最新索引
        userate = sqldata[9]  # pid使用率
        if userate > 0.9:
            sms.smssql('pid已满200个')
        new_id = int(last_id) + 1
        query = 'SELECT * FROM pidku WHERE id=' + str(new_id)
        curs.execute(query)
        sqldata = curs.fetchone()  # pidku表中取数据
        if sqldata:  # pidku未满
            adzone_id = sqldata[1]
            adzone_name = sqldata[2]
            site_id = sqldata[3]
            site_name = sqldata[4]
            new_create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            total = 186
            remain = total - new_id
            userate = new_id / total
            query = 'INSERT INTO pid VALUES ("%s", "%s", "%s", "%s", "%s", "%s", %d, %d, %d, %f)' % (
            adzone_id, adzone_name, site_id, site_name, openid, new_create_time, new_id, remain, total, userate)
            curs.execute(query)
            query = 'SELECT * FROM pid ORDER BY create_time desc limit 0,1'
        else:
            logger.warning('pidku已空，告急')
            sms.smssql('pidku已空，告急')
            query = 'SELECT * FROM pid WHERE adzone_name = "10001"'  # 使用本人pid进行购买
    curs.execute(query)
    sqldata = curs.fetchone()
    conn.commit()
    conn.close()
    return sqldata


def isneworder(order):
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    trade_id = str(order['trade_id'])
    query = 'SELECT * FROM orders WHERE trade_id="%s";' % (trade_id)
    curs.execute(query)
    sqldata = curs.fetchone()
    if sqldata:
        conn.close()
        return False
    else:
        conn.close()
        return True


def getorder(openid, trade_id):
    conn = sqlite3.connect('taobaokesql.db')
    conn.row_factory = dict_factory
    curs = conn.cursor()
    query = 'SELECT * FROM orders WHERE trade_id="%s" AND openid="%s";' % (trade_id, openid)
    curs.execute(query)
    sqldata = curs.fetchone()
    conn.close()
    return sqldata


def getOrderPlus(trade_id):
    conn = sqlite3.connect("taobaokesql.db")
    conn.row_factory = dict_factory
    curs = conn.cursor()
    args = (trade_id,)
    curs.execute('SELECT * FROM orders WHERE trade_id=?;', args)
    sqldata = curs.fetchone()
    conn.close()
    return sqldata


def addorder(orders):
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    for order in orders:
        if isneworder(order):
            if 'relation_id' in order:  # 存在relation_id 表示为渠道模式
                openid = ridopenid(order['relation_id'])
                if openid:
                    args = (order['site_id'], order['tk_create_time'], order['order_type'], order['click_time'],
                            order['commission_rate'], order['seller_nick'], order['price'], order['alipay_total_price'],
                            order['trade_id'], order['item_num'], order['adzone_id'], order['terminal_type'],
                            order['item_title'], order['tk_status'], order['seller_shop_title'], order['num_iid'],
                            order['subsidy_rate'], order['trade_parent_id'], order['relation_id'], order['income_rate'],
                            order['total_commission_rate'], order['pub_share_pre_fee'], order['item_category_name'],
                            "%s" % (openid))
                    curs.execute(
                        'INSERT INTO orders(site_id, create_time, order_type, click_time, commission_rate, '
                        'seller_nick, price, alipay_total_price, trade_id, item_num, adzone_id, terminal_type, '
                        'item_title, tk_status, seller_shop_title, num_iid, subsidy_rate, trade_parent_id, '
                        'relation_id, income_rate, total_commission_rate, pub_share_pre_fee, auction_category, '
                        'openid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                        args)
                    weixin.orderSuccess(openid[0], order, sqltbk(openid[0]).level_coefficient)
                    logger.info(openid[0] + ' 订单成功' + ' trade_id:' + str(order['trade_id']))
                else:
                    logger.warning('订单失败rid:' + str(order['relation_id']) + ' trade_id:' + str(order['trade_id']))
            else:  # 依然使用pid模式
                openid = pidopenid(order['adzone_id'])
                if openid:
                    args = (order['site_id'], order['tk_create_time'], order['order_type'], order['click_time'],
                            order['commission_rate'], order['seller_nick'], order['price'], order['alipay_total_price'],
                            order['trade_id'], order['item_num'], order['adzone_id'], order['terminal_type'],
                            order['item_title'], order['tk_status'], order['seller_shop_title'], order['num_iid'],
                            order['subsidy_rate'], order['trade_parent_id'], order['relation_id'], order['income_rate'],
                            order['total_commission_rate'], order['pub_share_pre_fee'], order['item_category_name'],
                            openid[4])
                    curs.execute(
                        'INSERT INTO orders(site_id, create_time, order_type, click_time, commission_rate, seller_nick, price, alipay_total_price, trade_id, item_num, adzone_id, terminal_type, item_title, tk_status, seller_shop_title, num_iid, subsidy_rate, trade_parent_id, relation_id, income_rate, total_commission_rate, pub_share_pre_fee, auction_category, openid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                        args)
                    weixin.orderSuccess(openid, order, sqltbk(openid).level_coefficient)
                    logger.info(openid + ' 订单成功' + ' trade_id:' + str(order['trade_id']))
                else:  # openid 不存在 找不到订单的主人
                    logger.warning('订单失败pid无法找到：' + str(order['adzone_id']) + ' trade_id:' + str(order['trade_id']))
    conn.commit()
    conn.close()
    return None


def OrderDetailsAdd(orders):
    conn = sqlite3.connect('taobaokesql.db')
    curs = conn.cursor()
    conn.row_factory = dict_factory  # 使数据库直接返回字典
    for order in orders:
        if isneworder(order):
            if 'relation_id' in order:  # 存在relation_id 表示为渠道模式
                openid = ridopenid(order['relation_id'])
                if openid:
                    args = (order['site_id'], order['tk_create_time'], order['order_type'], order['click_time'],
                            order['pub_share_rate'], order['seller_nick'], order['item_price'],
                            order['alipay_total_price'], order['trade_id'], order['item_num'], order['adzone_id'],
                            order['terminal_type'], order['item_title'], order['tk_status'], order['seller_shop_title'],
                            order['item_id'], order['subsidy_rate'], order['trade_parent_id'], order['relation_id'],
                            order['income_rate'], order['total_commission_rate'], order['pub_share_pre_fee'],
                            order['item_category_name'], "%s" % (openid))
                    curs.execute(
                        'INSERT INTO orders(site_id, create_time, order_type, click_time, commission_rate, '
                        'seller_nick, price, alipay_total_price, trade_id, item_num, adzone_id, terminal_type, '
                        'item_title, tk_status, seller_shop_title, num_iid, subsidy_rate, trade_parent_id, '
                        'relation_id, income_rate, total_commission_rate, pub_share_pre_fee, auction_category, '
                        'openid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                        args)
                    weixin.orderSuccess(openid[0], order, sqltbk(openid[0]).level_coefficient)
                    logger.info(openid[0] + ' 订单成功' + ' trade_id:' + str(order['trade_id']))
                else:
                    logger.warning('订单失败rid:' + str(order['relation_id']) + ' trade_id:' + str(order['trade_id']))
            else:  # 依然使用pid模式
                openid = pidopenid(order['adzone_id'])[4]
                if openid:
                    args = (order['site_id'], order['tk_create_time'], order['order_type'], order['click_time'],
                            order['seller_nick'], order['item_price'], order['alipay_total_price'], order['trade_id'],
                            order['item_num'], order['adzone_id'], order['terminal_type'], order['item_title'],
                            order['tk_status'], order['seller_shop_title'], order['item_id'], order['subsidy_rate'],
                            order['trade_parent_id'], order['income_rate'], order['total_commission_rate'],
                            order['pub_share_pre_fee'], order['item_category_name'], openid)
                    curs.execute('INSERT INTO orders(site_id, create_time, order_type, click_time, seller_nick, '
                                 'price, alipay_total_price, trade_id, item_num, adzone_id, terminal_type, '
                                 'item_title, tk_status, seller_shop_title, num_iid, subsidy_rate, trade_parent_id, '
                                 'income_rate, total_commission_rate, pub_share_pre_fee, auction_category, '
                                 'openid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', args)
                    weixin.orderSuccess(openid, order, sqltbk(openid).level_coefficient)
                    logger.info(openid + ' 订单成功' + ' trade_id:' + str(order['trade_id']))
                else:  # openid 不存在 找不到订单的主人
                    logger.warning('订单失败pid无法找到：' + str(order['adzone_id']) + ' trade_id:' + str(order['trade_id']))
    conn.commit()
    conn.close()
    return None


def orderMsgSuccess(item_title, trade_id, alipay_total_price, pub_share_pre_fee):  # 客服消息
    content = '———订单绑定———\n'
    content = content + item_title + '\n'
    content = content + '订单编号：\n'
    content = content + trade_id + '\n'
    content = content + '[红包]实付:' + str(alipay_total_price) + '\n'
    content = content + '[红包]红包:' + str(pub_share_pre_fee) + '\n'
    content = content + '— — — — — — — —\n'


class pid(object):
    def __init__(self, sqldata):
        self.adzone_id = sqldata[0]
        self.adzone_name = sqldata[1]
        self.site_id = sqldata[2]
        self.site_name = sqldata[3]
        self.openid = sqldata[4]
        self.create_time = sqldata[5]
        self.pidku_id = sqldata[6]
        self.remain = sqldata[7]
        self.total = sqldata[8]


if __name__ == "__main__":
    print("hi linksql.")
